import json

import numpy as np
import pandas as pd
# 读取数据
df1 = pd.read_excel('D:/fruit/fruit-backend-system/shuiguo/data/水果卖场产品销量数据.xls')
df2 = pd.read_excel('D:/fruit/fruit-backend-system/shuiguo/data/水果卖场会员数据.xls')
df3 = pd.read_excel('D:/fruit/fruit-backend-system/shuiguo/data/水果卖场销售数据.xls')
#异常数据处理
df1 = df1.dropna()
df2 = df2.dropna()
df3 = df3.dropna()
# 将两表数据合并
df = df1.merge(df3, on='卖场名称')
c_name= '华冠超市'

# ---------------------------------左上 散点图 -----------------------------------------------------------------
def markets():
    market = pd.unique(df2["卖场名称"])
    market.sort()
    market = list(market)
    return market
# print(markets())
def c_name_set(name):
    global c_name
    c_name = name

def h_task1():
    # 先筛选出Types字段为非酒精饮料的数据
    df_1 = df1[df1['卖场名称'] == '华冠超市']
    df_2 = df_1.groupby(['产品名称']).sum()
    df_3 = df_2['销量']
    data = df_1.loc[:,["产品名称","销量"]]
    tmp2 = pd.DataFrame(data).values.tolist()
    #print(tmp2)

    # 转换为DataFrame
    tmp1 = pd.DataFrame(df_3)
    # 将值和索引转换为列表
    v = tmp1.values.tolist()
    name = tmp1.index.tolist()
    # 因为值转换为列表格式为[[],[],[],...],所以创建新列表，将值列表中的值取出
    value = []
    for i in v:
        value.append(i[0])
    # 返回字典格式数据
    return {
        'data':tmp2,
        'name': name,  # 商品名
        'value': value  # 商品数
    }
# # ---------------------------------左中 条形图-------------------------------------------------------------------
def h_task2():
    df_1 = df1[df1['卖场名称'] == '华冠超市']
    df_4 = df_1.groupby(['产品名称', '产品分类']).size()
    datas = pd.DataFrame(df_4).index.tolist()
    #print(datas)
    x = np.array([ 0, 30,  60 , 90, 120, 150, 180 ,210,240,0, 30,  60 , 90, 120, 150, 180 ,210, 240])
    y = np.array([20,20,20,20,20,20,20,20,20,110,110,110,110,110,110,110,110,110])

    name = []
    name1 = []
    newdata1 = []
    newdata2 = []
    data1 = []
    for i in range(0,len(datas)):

            name.append(datas[i][0]),
            name1.append(datas[i][1])
            newdata2.append({
                "source": datas[i][0],
                "target": datas[i][1]
            })
    #print(data1)
    he = pd.unique(name + name1)
    print(len(he))
    for i in range(0,len(he)):
        newdata1.append(
            {
                "name": he[i],
                "x": int(x[i]),
                "y": int(y[i])
            }
        )


    # print(name+name1)
    # 返回字典格式数据
    return {
        "data":newdata1,
        "link":newdata2
    }
# print(h_task2())
# # ---------------------------------左下 瀑布图 -------------------------------------------------------------------
def h_task3():
    df_1=df.groupby(['卖场名称'], group_keys=True)['销售预算']
    df_2 = pd.DataFrame(df_1)
    df_3 = df_2.values.tolist()
    name = []
    value = []
    for i in range(0,len(df_3)):
        name.append(df_3[i][0][0])
        value.append(list(df_3[i][1].head(102)))
    print(name)
    return {
        "index":name,
        "value":value
    }

# print(h_task3())
# # ---------------------------------中上 折线图 -------------------------------------------------------------------
def h_task4():
    df_1 = df3[df3['卖场名称'] == '华冠超市']
    df_2 = df_1["销售预算"]
    tmp2 = pd.DataFrame(df_2).values.tolist()
    print(tmp2)
    value = []
    for i in tmp2:
        value.append(i[0])
    print(value)
    return {
        "values":value
    }

# print(h_task4())
# # --------------------------------- 中中 雷达图-------------------------------------------------------------------
def h_task5():
    df_1 = df3[df3['卖场名称'] == '华冠超市']
    AC = int(df_1["AC"].sum())
    TC = int(df_1["TC"].sum())
    sale = int(df_1["销售"].sum())

    return (
        {
            "AC": AC,
            "TC": TC,
            "销售": sale
        }
    )
# # --------------------------------- 中下 玫瑰图-------------------------------------------------------------------
def h_task6():
    df_1 = df[df['卖场名称'] == '华冠超市']
    sale = int(df_1["销量"].max())
    avg = int(df_1["销量"].sum() / df_1["销量"].count())
    df_2 = df_1.groupby(["省份名称"]).sum()
    df_3 = df_1.groupby(["省份名称"]).count()

    print(df_2["销量"])
    name = df_2["销量"].index.tolist()
    value1 = df_2["销量"].values.tolist()
    value2 = df_3["销量"].values.tolist()
    sale1 = []
    sale2 = []
    for i in range(0,len(name)):
        sale1.append({
            "name":name[i],
            "value":value1[i]
        })
        sale2.append({
            "name":name[i],
            "value":value1[i]/value2[i]
        })
    return {
            "max_销量":sale,
            "max_门店平均销量":avg,
            "销量":sale1,
            "门店平均销量":sale2
        }

# # --------------------------------- 右上 环形图-------------------------------------------------------------------
def h_task7():
    df_1 = df[df['卖场名称'] == '华冠超市']
    df_2 =df_1.groupby(["产品分类"]).sum()
    df_3 = df_2["销量"]
    print(df_3)
    index = pd.DataFrame(df_3).index.tolist()
    v = pd.DataFrame(df_3).values.tolist()
    value = []
    for i in v:
        value.append(i[0])
    print(v)
    return {
        # "data":df_2,
        "产品分类": index,
        "销量":value
    }

# print(h_task7())
# # --------------------------------- 右中 旭日图-------------------------------------------------------------------
def h_task8():
    df_1 = df1.groupby(["卖场名称"]).sum()
    total = int(df_1["销量"].sum())
    print(total)
    df_2 = df1[df1["卖场名称"]=="华冠超市"]
    name = set(df_2["卖场名称"])
    value = int(df_2["销量"].sum())
    print(value)
    return {
        'name':list(name),
        'total': total,
        'value': value
    }
print(h_task8())
# # --------------------------------- 右下 树图-------------------------------------------------------------------
def h_task9():
    df_1 = df2[df2["卖场名称"]=="华冠超市"]
    df_2= df_1.groupby(["月份"]).sum()
    df_3 = df_2["会员增加数"]
    data = pd.DataFrame(df_3)
    name = data.index.tolist()
    value = data.values.tolist()
    v = []
    for i in value:
        v.append(int(i[0]))
    return {
        'name':name,
        'value':v
    }
# print(h_task9())